import threading
import time
from datetime import datetime, timedelta
import logging
import json
from typing import Dict, List, Any
import mysql.connector
from mysql.connector import Error
import traceback
from excelhelper_xue import excelhelper_xue
import math
import re

class BigModelSystem:
    def __init__(self):
        self.logger = self.setup_logger()
        self.running = False
        self.scheduler_thread = None
        self.excelhelper_instance = excelhelper_xue()
         
    def setup_logger(self):
        """设置日志记录器"""
        logger = logging.getLogger('BigModelSystem')
        logger.setLevel(logging.INFO)
        
        # 创建文件处理器
        file_handler = logging.FileHandler('bigmodel.log')
        file_handler.setLevel(logging.INFO)
        
        # 创建控制台处理器
        console_handler = logging.StreamHandler()
        console_handler.setLevel(logging.INFO)
        
        # 创建格式化器
        formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
        file_handler.setFormatter(formatter)
        console_handler.setFormatter(formatter)
        
        # 添加处理器
        logger.addHandler(file_handler)
        logger.addHandler(console_handler)
        
        return logger

     

    def collect_product_data(self):
        """收集所有产品数据及其历史记录"""
        try:
            # 获取所有产品数据
            products_query = """
        SELECT 
            p.productid,
            p.title,
            p.sales_info,
            p.rating,
            p.reviews_count,
            p.sub_rank,
                p.main_rank
            FROM app_product_xue p limit 10000
            """
            products = self.excelhelper_instance.query_one(products_query)
            
            if not products:
                return []
            
            # 获取所有产品的历史记录
            product_ids = [p['productid'] for p in products]
            history_query = """
            SELECT 
                productid,
                old_sub_rank,
                new_sub_rank,
                old_reviews_count,
                new_reviews_count,
                old_rating,
                new_rating,
                old_sales_info,
                new_sales_info,
                opdate
            FROM app_rankchange_xue
            WHERE productid IN :product_ids
            ORDER BY productid, opdate DESC
            """
            
            # 使用参数化查询，避免SQL注入
            history_records = self.excelhelper_instance.query_one(history_query, {'product_ids': tuple(product_ids)})
            
            # 将历史记录按产品ID分组
            history_by_product = {}
            for record in history_records:
                productid = record['productid']
                if productid not in history_by_product:
                    history_by_product[productid] = []
                history_by_product[productid].append(record)
            
            # 处理每个产品
            # product_data_list=self.list_to_dictlist(products)
            product_data_list=self.list_to_dictlist_product(products)
            for product in product_data_list:
                productid = product['productid']
                if len(history_by_product) and history_by_product.get(productid, []) :
                    product['history']=history_by_product.get(productid, [])
                else:
                    product['history']=[]
                
                # 计算飙升指数
                if product['history']:
                    latest_change = product['history'][0]  # 最新的变化记录
                    rank_change = float(latest_change['new_sub_rank'] if latest_change['new_sub_rank']!=None else 0) - float(latest_change['old_sub_rank'] if latest_change['old_sub_rank']!=None else 0)
                    review_change = float(latest_change['new_reviews_count'] if latest_change['new_reviews_count']!=None else 0) - float(latest_change['old_reviews_count'] if latest_change['old_reviews_count']!=None else 0)
                    sales_change = float(latest_change['new_sales_info'] if latest_change['new_sales_info']!=None else 0) - float(latest_change['old_sales_info'] if latest_change['old_sales_info']!=None else 0)
                    
                    # 计算飙升指数（可以根据需要调整权重）
                    surge_index = (
                        abs(rank_change) * 0.4 +  # 排名变化权重
                        abs(review_change) * 0.3 +  # 评论变化权重
                        abs(sales_change) * 0.3    # 销量变化权重
                    )
                    
                    product['surge_index'] = surge_index
                    product['is_surging'] = surge_index > 50  # 设置飙升阈值
                else:
                    product['surge_index'] = 0
                    product['is_surging'] = False   
            
            return products
            
        except Exception as e:
            traceback.print_exc()
            print(f"收集产品数据时出错: {str(e)}")
            return []

    def analyze_product_potential(self, product_data: Dict[str, Any]) -> Dict[str, Any]:
        """分析产品潜力"""
        analysis = {
            'market_demand': self.calculate_market_demand(product_data),
            'competition_level': self.analyze_competition(product_data),
            'profitability': self.calculate_profitability(product_data),
            'growth_potential': self.assess_growth_potential(product_data)
        }
        return analysis

    def optimize_title(self, current_title: str, product_data: Dict[str, Any]) -> Dict[str, Any]:
        """优化产品标题"""
        optimized_title = {
            'current_title': current_title,
            'suggested_title': self.generate_optimized_title(current_title, product_data),
            'improvement_areas': self.identify_title_improvements(current_title),
            'keyword_suggestions': self.suggest_keywords(product_data)
        }
        return optimized_title

    def optimize_bullet_points(self, current_bullets: List[str], product_data: Dict[str, Any]) -> List[Dict[str, Any]]:
        """优化五点描述"""
        optimized_bullets = []
        for bullet in current_bullets:
            optimized_bullet = {
                'current': bullet,
                'suggested': self.optimize_single_bullet(bullet, product_data),
                'improvements': self.suggest_bullet_improvements(bullet)
            }
            optimized_bullets.append(optimized_bullet)
        return optimized_bullets

    def generate_predictions(self, product_data: Dict[str, Any]) -> Dict[str, Any]:
        """生成预测"""
        try:
            # 计算各项预测
            rank = self.predict_rank(product_data)
            reviews = self.predict_reviews(product_data)
            rating = self.predict_rating(product_data)
            sales = self.predict_sales(product_data)
            confidence_score = self.calculate_confidence_score(product_data, {
                'rank': rank,
                'reviews': reviews,
                'rating': rating,
                'sales': sales 
            })
            rank.update(reviews)
            rank.update(rating)
            rank.update(sales)
            rank.update(confidence_score)
            self.save_predictions(rank)
            return rank
        except Exception as e:
            traceback.print_exc()
            self.logger.error(f"生成预测出错: {e}")
            return {}

    def calculate_confidence_score(self, product_data: Dict[str, Any], predictions: Dict[str, Any]) -> float:
        """计算预测的置信度分数"""
        try:
            score = 0.0
            factors = 0
            product_data_list=self.list_to_dictlist(product_data)
            for product in product_data_list:
                # 1. 数据完整性检查
                if product.get('new_main_rank') is not None:
                    score += 0.2
                factors += 1
                if product.get('new_reviews_count') is not None:
                    score += 0.2
                    factors += 1
                if product.get('new_rating') is not None:
                    score += 0.2
                    factors += 1
                if product.get('new_sales_info') is not None:
                    score += 0.2
                    factors += 1

                
                # 2. 历史数据可用性检查
                if product.get('old_sub_rank') is not None and product.get('new_sub_rank') is not None:
                    score += 0.2
                    factors += 1
                if product.get('old_reviews_count') is not None and product.get('new_reviews_count') is not None:
                    score += 0.2
                    factors += 1
                if product.get('old_rating') is not None and product.get('new_rating') is not None:
                    score += 0.2
                    factors += 1
                
                # # 3. 预测合理性检查
                # if predictions['new_rank'] > 0:
                #     score += 0.1
                #     factors += 1
                # if predictions['reviews'] > 0:
                #     score += 0.1
                #     factors += 1
                # if 0 <= predictions['rating'] <= 5:
                #     score += 0.1
                #     factors += 1
                # if predictions['sales'] != "0":
                #     score += 0.1
                #     factors += 1
                # if predictions['price'] > 0:
                #     score += 0.1
                #     factors += 1
                
                # 计算最终置信度分数 (0-100)
                confidence_score=0
                if factors > 0:
                    confidence_score = (score / factors) * 100
                else:
                    confidence_score = 0.0
                product['confidence_score']=confidence_score
            
            return product_data_list
        except Exception as e:
            self.logger.error(f"计算置信度分数出错: {e}")
            traceback.print_exc()
            return 0.0

    def save_predictions(self, predictions):
        """保存预测结果"""
        try:
            query = """
            INSERT INTO app_product_prediction (
                productid,
                predicted_rank,
                predicted_reviews,
                predicted_rating,
                predicted_sales,
                prediction_date,
                confidence_score,
                model_version,
                old_sub_rank,
                new_sub_rank,
                old_reviews_count,
                new_reviews_count,
                old_rating,
                new_rating,
                old_sales_info,
                new_sales_info,
                opdate
            ) VALUES (
                :productid,
                :predicted_rank,
                :predicted_reviews,
                :predicted_rating,
                :predicted_sales,
                :predicted_price,
                now(),
                :confidence_score,
                :model_version,
                :old_sub_rank,
                :new_sub_rank,
                :old_reviews_count,
                :new_reviews_count,
                :old_rating,
                :new_rating,
                :old_sales_info,
                :new_sales_info,
                now()
            )
            """
            
            
            self.excelhelper_instance.execute(query, predictions)
            
        except Error as e:
            self.logger.error(f"保存预测结果出错: {e}")

    # 辅助方法
    def calculate_market_demand(self, product_data: List[Dict[str, Any]]) -> float:
        """计算市场需求"""
        try:
            if not product_data:
                return 0.0
            
            total_demand = 0.0
            product_count = 0
            
            for product in product_data:
                # 获取销量和评论数
                sales = float(str(product.get('sales_info', '0')).replace(',', '')) if product.get('sales_info') else 0
                reviews = int(product.get('reviews_count', 0)) if product.get('reviews_count') else 0
                
                # 计算单个产品的需求指数
                if sales > 0 and reviews > 0:
                    # 评论转化率
                    review_conversion = reviews / sales
                    # 需求指数 = 销量 * 评论转化率
                    demand_index = sales * review_conversion
                    total_demand += demand_index
                    product_count += 1
            
            # 计算平均市场需求
            if product_count > 0:
                avg_demand = total_demand / product_count
                # 归一化到0-100范围
                normalized_demand = min(100, max(0, avg_demand / 1000))
                return round(normalized_demand, 2)
            
            return 0.0
        except Exception as e:
            self.logger.error(f"计算市场需求出错: {e}")
            return 0.0

    def analyze_competition(self, product_data: List[Dict[str, Any]]) -> float:
        """分析竞争程度"""
        try:
            if not product_data:
                return 0.0
            
            total_competition = 0.0
            product_count = 0
            
            for product in product_data:
                # 基于排名和评分分析竞争程度
                rank = product.get('main_rank', 0)
                rating = product.get('rating', 0)
                
                # 排名越靠前，竞争越激烈
                rank_factor = 1 - (min(rank, 10000) / 10000)
                
                # 评分越高，竞争越激烈
                rating_factor = rating / 5.0
                
                # 综合竞争指数
                competition_index = (rank_factor * 0.6 + rating_factor * 0.4) * 100
                total_competition += competition_index
                product_count += 1
            
            # 计算平均竞争程度
            if product_count > 0:
                return round(total_competition / product_count, 2)
            
            return 0.0
        except Exception as e:
            self.logger.error(f"分析竞争程度出错: {e}")
            return 0.0

    def calculate_profitability(self, product_data: List[Dict[str, Any]]) -> float:
        """计算盈利能力"""
        try:
            if not product_data:
                return 0.0
            
            total_profitability = 0.0
            product_count = 0
            
            for product in product_data:
                # 基于价格、销量和评分计算盈利能力
                price = float(product.get('price', 0)) if product.get('price') else 0
                sales = float(str(product.get('sales_info', '0')).replace(',', '')) if product.get('sales_info') else 0
                rating = product.get('rating', 0) if product.get('rating') else 0
                
                # 计算收入
                revenue = price * sales
                
                # 计算利润率 (假设固定成本比例)
                profit_margin = 0.3  # 30%利润率
                
                # 考虑评分对盈利能力的影响
                rating_factor = rating / 5.0
                
                # 计算盈利能力指数
                profitability = (revenue * profit_margin * rating_factor) / 1000
                
                # 归一化到0-100范围
                normalized_profitability = min(100, max(0, profitability))
                total_profitability += normalized_profitability
                product_count += 1
            
            # 计算平均盈利能力
            if product_count > 0:
                return round(total_profitability / product_count, 2)
            
            return 0.0
        except Exception as e:
            self.logger.error(f"计算盈利能力出错: {e}")
            return 0.0

    def assess_growth_potential(self, product_data: Dict[str, Any]) -> float:
        """评估增长潜力"""
        try:
            list_product_data = self.list_to_dictlist(product_data)
            for product in list_product_data:
                # 基于历史数据评估增长潜力
                old_rank = product.get('old_sub_rank', 0)
                new_rank = product.get('new_sub_rank', 0)
                old_reviews = product.get('old_reviews_count', 0)
                new_reviews = product.get('new_reviews_count', 0)
                    
                # 计算排名提升率
                rank_improvement = (old_rank - new_rank) / max(old_rank, 1) if old_rank > 0 else 0
                
                # 计算评论增长率
                review_growth = (new_reviews - old_reviews) / max(old_reviews, 1) if old_reviews > 0 else 0
                
                # 综合增长潜力
                growth_potential = (rank_improvement * 0.6 + review_growth * 0.4) * 100
                product['growth_potential']=growth_potential
            
            return list_product_data
        except Exception as e:
            self.logger.error(f"评估增长潜力出错: {e}")
            return 0.0

    def generate_optimized_title(self, current_title: str, product_data: Dict[str, Any]) -> str:
        """生成优化后的标题"""
        try:
            list_product_data = self.list_to_dictlist(product_data)
            for product in list_product_data:
                # 提取关键词
                keywords = self.extract_keywords(product_data)
                    
                # 获取产品类别
                category = product_data.get('category', '')
                
                # 获取主要卖点
                main_features = self.extract_main_features(product_data)
                
                # 构建优化后的标题
                optimized_title = f"{category} {main_features[0]} {keywords[0]} {main_features[1]}"
                
                # 确保标题长度合适
                if len(optimized_title) > 200:
                    optimized_title = optimized_title[:197] + "..."
                product['optimized_title']=optimized_title
            return optimized_title
        except Exception as e:
            self.logger.error(f"生成优化标题出错: {e}")
            return current_title

    def identify_title_improvements(self, product_data: Dict[str, Any]      ) -> List[str]:
        """识别标题改进点"""
        try:
            list_product_data = self.list_to_dictlist(product_data)
            for product in list_product_data:
                keywords = self.extract_keywords(product.get('title', ''))
                category = product.get('category', '')
                main_features = self.extract_main_features(product)     
        except Exception as e:
            self.logger.error(f"识别标题改进点出错: {e}")
            return list_product_data        
        # 实现标题改进点识别逻辑
        pass

    def suggest_keywords(self, product_data: Dict[str, Any]) -> List[str]:
        """建议关键词"""
        # 实现关键词建议逻辑
        try:
            list_product_data = self.list_to_dictlist(product_data)
            for product in list_product_data:
                # 提取关键词
                keywords = self.extract_keywords(product.get('title', ''))
                
        except Exception as e:
            self.logger.error(f"识别标题改进点出错: {e}")
            traceback.print_exc()

            return list_product_data         
        pass

    def optimize_single_bullet(self, bullet: str, product_data: Dict[str, Any]) -> str:
        """优化单个描述点"""
        try:
            product_data_list    = self.list_to_dictlist(product_data)
            for product in product_data_list:
                # 提取关键信息
                features = self.extract_features(bullet)
                
                # 获取产品优势
                advantages = self.get_product_advantages(product_data)
                
                # 构建优化后的描述
                optimized_bullet = f"{features[0]}: {advantages[0]}. {features[1]}: {advantages[1]}"
                product['optimized_bullet']=optimized_bullet
            return product_data_list
        except Exception as e:
            self.logger.error(f"优化描述点出错: {e}")
            return bullet

    def suggest_bullet_improvements(self, bullet: str) -> List[str]:
        """建议描述点改进"""
        # 实现描述点改进建议逻辑
        pass

    def list_to_dictlist(self,product_data: Dict[str, Any]) -> List[Dict[str, Any]]:
        """将列表转换为字典列表"""
        new_product_list=[]
        for pd in product_data:
            new_product_list.append({'productid':pd['productid'], 
                                    'old_rating':pd['old_rating'],'new_rating':pd['new_rating'], 
                                     'old_sub_rank':pd['old_sub_rank'],'new_sub_rank':pd['new_sub_rank'],'old_sales_info':pd['old_sales_info'],
                                     'new_sales_info':pd['new_sales_info']})
        return new_product_list
    def list_to_dictlist_product(self,product_data: Dict[str, Any]) -> List[Dict[str, Any]]:
        """将列表转换为字典列表"""
        new_product_list=[]   
 
        for pd in product_data:
            new_product_list.append({'productid':pd['productid'],'title':pd['title'],'rating':pd['rating'],'reviews_count':pd['reviews_count'],
                                     'sales_info':pd['sales_info'],'main_rank':pd['main_rank'],'sub_rank':pd['sub_rank']})
        return new_product_list    

    def predict_rank(self, product_data: Dict[str, Any]) -> int:
        """预测排名"""
        try:

            # 确保product_data是字典类型
            new_product_list=self.list_to_dictlist(product_data)
            for pd in new_product_list:
                current_rank = int(pd.get('main_rank', 0) if pd.get('main_rank')!=None else 0)
                old_rank = int(pd.get('old_sub_rank', 0) if pd.get('old_sub_rank')!=None else 0)
                new_rank = int(pd.get('new_sub_rank', 0) if pd.get('new_sub_rank')!=None else 0)
                print(current_rank,old_rank,new_rank)
                
                # 计算排名变化趋势
                if old_rank and new_rank:
                    rank_change = new_rank - old_rank
                    predicted_rank = current_rank + (rank_change * 1.2)  # 假设趋势继续
                else:
                    predicted_rank = current_rank
                pd['predicted_rank']=int(predicted_rank)
            return new_product_list
            # return max(1, int(predicted_rank))
        except Exception as e:
            self.logger.error(f"预测排名出错: {e}")
            traceback.print_exc()

            return 0

    def predict_reviews(self, product_data: Dict[str, Any]) -> int:
        """预测评论数"""
        try:
            # 确保product_data是字典类型
             
            list_product_data = self.list_to_dictlist(product_data)
            predicted_reviews_list=[]
            for product in list_product_data:  
                current_reviews = int(product.get('reviews_count', 0))
                old_reviews = int(product.get('old_reviews_count', 0))
                new_reviews = int(product.get('new_reviews_count', 0))
                predicted_reviews=0
                # 计算评论增长率
                if old_reviews and new_reviews:
                    growth_rate = (new_reviews - old_reviews) / old_reviews
                    predicted_reviews = current_reviews * (1 + growth_rate)
                else:
                    predicted_reviews = current_reviews * 1.01  # 默认1%增长率
                product['predicted_reviews']=int(predicted_reviews)
            return list_product_data

        except Exception as e:
            self.logger.error(f"预测评论数出错: {e}")
            traceback.print_exc()

            return 0

    def predict_rating(self, product_data: Dict[str, Any]) -> float:
        """预测评分"""
        try:
            # 确保product_data是字典类型
            list_product_data = self.list_to_dictlist(product_data)
            for product in list_product_data:  
                current_rating = float(product.get('rating', 0)) if product.get('rating')!=None else 0
                old_rating = float(product.get('old_rating', 0)) if product.get('old_rating')!=None else 0
                new_rating = float(product.get('new_rating', 0)) if product.get('new_rating')!=None else 0
            
                # 计算评分趋势
                if old_rating and new_rating:
                    rating_change = new_rating - old_rating
                    predicted_rating = current_rating + (rating_change * 0.5)  # 评分变化较慢
                else:
                    predicted_rating = current_rating
                product['predicted_rating']=round(max(0, min(5, predicted_rating)), 1)
            return list_product_data
        except Exception as e:
            self.logger.error(f"预测评分出错: {e}")
            return 0.0

    def predict_sales(self, product_data: Dict[str, Any]) -> str:
        """预测销量"""
        try:
            # 确保product_data是字典类型
            list_product_data = self.list_to_dictlist(product_data)
            for product in list_product_data:  
                # 安全地获取值，处理None和空字符串
                current_sales = float(str(product.get('sales_info', '0')).replace(',', '')) if product.get('sales_info') else 0
                rank = int(product.get('main_rank', 0)) if product.get('main_rank') else 10000  # 默认值为10000，表示排名靠后
                rating = float(product.get('rating', 0)) if product.get('rating') else 0
                
                # 计算排名因子，排名越靠前（数字越小）因子越大
                # 使用对数函数来平滑排名影响，避免极端值
                rank_factor = 1 / (math.log10(max(rank, 1)) + 1)  # +1 避免log(1)=0的情况
                
                # 计算评分因子
                rating_factor = rating / 5.0 if rating > 0 else 0.1  # 默认评分因子为0.1
                
                # 计算预测销量
                # 排名第一的产品销量预测应该比排名靠后的高
                predicted_sales = current_sales * (1 + rank_factor * 2.0 + rating_factor * 0.4)
                
                # 确保预测销量不为负
                predicted_sales = max(0, predicted_sales)
                
                product['predicted_sales'] = int(predicted_sales)
                
            return list_product_data
        except Exception as e:
            self.logger.error(f"预测销量出错: {e}")
            traceback.print_exc()
            return "0"

    def start_scheduler(self):
        """启动调度器"""
        self.running = True
        self.scheduler_thread = threading.Thread(target=self._scheduler_loop)
        self.scheduler_thread.daemon = True
        self.scheduler_thread.start()
        self.logger.info("调度器已启动")

    def stop_scheduler(self):
        """停止调度器"""
        self.running = False
        if self.scheduler_thread:
            self.scheduler_thread.join()
        self.logger.info("调度器已停止")

    def _scheduler_loop(self):
        """调度器主循环"""
        while self.running:
            try:
                # 执行数据收集和预测
                self.collect_and_predict()
                
                
                
            except Exception as e:
                self.logger.error(f"调度器执行出错: {str(e)}")
                time.sleep(60)  # 出错后等待1分钟再重试

    def collect_and_predict(self):
        """收集数据并生成预测"""
        try:
            self.logger.info("开始收集数据...")
            # 收集数据
            product_datas = self.collect_product_data()
            
            if not product_datas:
                self.logger.warning("没有收集到产品数据")
                return
            
            # 生成预测
            history_query = """
            SELECT 
                *
            FROM app_rankchange_xue
             
            ORDER BY productid, opdate DESC
            """
            product_ids = [{'productid':p['productid']} for p in product_datas]

            # 使用参数化查询，避免SQL注入
            history_records = self.excelhelper_instance.query_one(history_query)
            predictions = self.generate_predictions(history_records)
            
            # 添加产品ID到预测结果中
            predictions['productid'] = product_datas.get('productid', 'unknown')
            
            # 保存预测结果
            self.save_predictions(predictions)
            
            self.logger.info(f"成功处理产品 {product_datas.get('productid', 'unknown')} 的预测")
            
        except Exception as e:
            self.logger.error(f"数据收集和预测出错: {str(e)}")

    def save_analysis_results(self, product_data: List[Dict[str, Any]], analysis_results: Dict[str, Any], epid: str = None):
        """
        保存分析结果到数据库
        
        参数:
            product_data: 产品数据列表
            analysis_results: 分析结果
            epid: 部门编号，默认为None
        """
        try:
            query = """
            INSERT INTO app_product_analysis (
                productid,
                market_demand,
                competition_level,
                profitability,
                growth_potential,
                optimized_title,
                optimized_bullets,
                keyword_suggestions,
                confidence_score,
                epid
            ) VALUES (
                :productid,
                :market_demand,
                :competition_level,
                :profitability,
                :growth_potential,
                :optimized_title,
                :optimized_bullets,
                :keyword_suggestions,
                :confidence_score,
                :epid
            )
            """
            
            for product in product_data:
                data = {
                    'productid': product.get('productid'),
                    'market_demand': analysis_results.get('market_demand', 0),
                    'competition_level': analysis_results.get('competition_level', 0),
                    'profitability': analysis_results.get('profitability', 0),
                    'growth_potential': analysis_results.get('growth_potential', 0),
                    'optimized_title': analysis_results.get('optimized_title', ''),
                    'optimized_bullets': json.dumps(analysis_results.get('optimized_bullets', [])),
                    'keyword_suggestions': json.dumps(analysis_results.get('keyword_suggestions', [])),
                    'confidence_score': analysis_results.get('confidence_score', 0),
                    'epid': epid or 'default'  # 使用提供的epid或默认值
                }
                
                self.excelhelper_instance.execute(query, data)
                
            self.logger.info("分析结果已保存到数据库")
            
        except Exception as e:
            self.logger.error(f"保存分析结果出错: {e}")
            traceback.print_exc()

    def extract_keywords(self, product_data):
        """提取产品关键词"""
        try:
            keywords = set()
            
            # 从标题中提取关键词
            if product_data.get('title'):
                title = product_data['title'].lower()
                # 移除常见词
                common_words = {'the', 'and', 'for', 'with', 'in', 'on', 'at', 'to', 'of', 'a', 'an'}
                words = [word for word in title.split() if word not in common_words and len(word) > 2]
                keywords.update(words)
            
            # 从详情中提取关键词
            if product_data.get('details'):
                for detail in product_data['details']:
                    detail = detail.lower()
                    # 提取括号中的内容
                    if '(' in detail and ')' in detail:
                        keywords.update(re.findall(r'\((.*?)\)', detail))
                    # 提取引号中的内容
                    if '"' in detail:
                        keywords.update(re.findall(r'"([^"]*)"', detail))
                    # 提取冒号后的内容
                    if ':' in detail:
                        keywords.update(detail.split(':')[1].strip().split())
            
            # 从类别中提取关键词
            if product_data.get('maincategory'):
                keywords.update(product_data['maincategory'].lower().split())
            if product_data.get('sun_subcategory'):
                keywords.update(product_data['sun_subcategory'].lower().split())
            
            # 清理和过滤关键词
            keywords = {
                keyword.strip('.,!?()[]{}"\'') 
                for keyword in keywords 
                if len(keyword) > 2 and not keyword.isdigit()
            }
            
            return list(keywords)
            
        except Exception as e:
            print(f"提取关键词时出错: {str(e)}")
            return []

    def extract_features(self, product_data):
        """提取产品特征"""
        try:
            features = []
            
            # 从详情中提取特征
            if product_data.get('details'):
                for detail in product_data['details']:
                    detail = detail.strip()
                    # 跳过空行和过短的描述
                    if not detail or len(detail) < 10:
                        continue
                        
                    # 提取特征描述
                    if ':' in detail:
                        feature = detail.split(':', 1)[1].strip()
                    else:
                        feature = detail
                    
                    # 清理特征描述
                    feature = feature.strip('.,!?()[]{}"\'')
                    if feature and len(feature) > 5:
                        features.append(feature)
            
            # 从标题中提取特征
            if product_data.get('title'):
                title = product_data['title']
                # 提取括号中的特征
                if '(' in title and ')' in title:
                    features.extend(re.findall(r'\((.*?)\)', title))
                # 提取引号中的特征
                if '"' in title:
                    features.extend(re.findall(r'"([^"]*)"', title))
            
            # 去重和清理
            features = list(set(features))
            features = [f for f in features if len(f) > 5]
            
            return features
            
        except Exception as e:
            print(f"提取特征时出错: {str(e)}")
            return []

    def get_product_advantages(self, product_data):
        """获取产品优势"""
        try:
            advantages = []
            
            # 从评分和评论中提取优势
            if product_data.get('rating') and float(product_data['rating']) >= 4.0:
                advantages.append(f"高评分产品 ({product_data['rating']}星)")
            if product_data.get('reviewCount'):
                review_count = int(product_data['reviewCount'].replace(',', ''))
                if review_count > 100:
                    advantages.append(f"超过{review_count}条好评")
            
            # 从排名中提取优势
            if product_data.get('main_rank'):
                rank = product_data['main_rank']
                if '#' in rank:
                    rank_num = int(rank.split('#')[1].split()[0].replace(',', ''))
                    if rank_num <= 100:
                        advantages.append(f"畅销榜排名前{rank_num}")
            
            # 从特征中提取优势
            features = self.extract_features(product_data)
            for feature in features:
                # 识别优势相关的关键词
                advantage_keywords = {
                    'durable', 'long lasting', 'high quality', 'premium',
                    'easy to use', 'convenient', 'portable', 'lightweight',
                    'versatile', 'multifunctional', 'efficient', 'powerful',
                    'eco-friendly', 'environmental', 'sustainable'
                }
                
                feature_lower = feature.lower()
                for keyword in advantage_keywords:
                    if keyword in feature_lower:
                        advantages.append(feature)
                        break
            
            # 从销量中提取优势
            if product_data.get('sales_info'):
                sales = float(product_data['sales_info'].replace(',', ''))
                if sales > 1000:
                    advantages.append(f"月销量超过{sales}件")
            
            # 去重和排序
            advantages = list(set(advantages))
            advantages.sort(key=lambda x: len(x), reverse=True)
            
            return advantages
            
        except Exception as e:
            print(f"获取产品优势时出错: {str(e)}")
            return []

    def optimize_title_by_similar_products(self, target_asin: str, category: str = None, limit: int = 100) -> Dict[str, Any]:
        """通过分析同类产品优化标题"""
        try:
            # 获取目标产品信息
            target_query = """
            SELECT 
                p.productid,
                p.title,
                p.maincategory,
                p.sun_subcategory
            FROM app_product_xue p
            WHERE p.productid = :asin
            """
            target_product = self.excelhelper_instance.query_one(target_query, {'asin': target_asin})
            
            if not target_product:
                return {'error': '目标产品不存在'}
            
            # 确定搜索类别
            search_category = category or target_product.get('maincategory') or target_product.get('sun_subcategory')
            if not search_category:
                return {'error': '无法确定产品类别'}
            
            # 获取同类产品
            similar_query = """
            SELECT 
                p.title,
                p.rating,
                p.reviews_count,
                p.sales_info,
                p.main_rank
            FROM app_product_xue p
            WHERE (p.maincategory = :category OR p.sun_subcategory = :category)
            AND p.productid != :asin
            ORDER BY 
                p.rating DESC,
                p.reviews_count DESC,
                p.sales_info DESC
            LIMIT :limit
            """
            
            similar_products = self.excelhelper_instance.query_all(
                similar_query, 
                {'category': search_category, 'asin': target_asin, 'limit': limit}
            )
            
            if not similar_products:
                return {'error': '未找到同类产品'}
            
            # 分析标题模式
            title_patterns = {
                'keywords': set(),
                'features': set(),
                'brands': set(),
                'specs': set()
            }
            
            # 提取关键词和模式
            for product in similar_products:
                title = product['title'].lower()
                
                # 提取品牌名（通常在标题开头）
                brand = title.split()[0]
                if brand.isupper() or brand[0].isupper():
                    title_patterns['brands'].add(brand)
                
                # 提取规格（通常在括号中）
                specs = re.findall(r'\((.*?)\)', title)
                title_patterns['specs'].update(specs)
                
                # 提取特征词
                feature_words = {
                    'premium', 'professional', 'advanced', 'deluxe',
                    'high quality', 'best', 'top', 'excellent',
                    'durable', 'long lasting', 'reliable', 'sturdy'
                }
                for word in feature_words:
                    if word in title:
                        title_patterns['features'].add(word)
                
                # 提取关键词（排除常见词）
                common_words = {'the', 'and', 'for', 'with', 'in', 'on', 'at', 'to', 'of', 'a', 'an'}
                words = [word for word in title.split() if word not in common_words and len(word) > 2]
                title_patterns['keywords'].update(words)
            
            # 构建优化后的标题
            current_title = target_product['title']
            optimized_title = current_title
            
            # 1. 确保品牌名在开头（如果适用）
            if title_patterns['brands']:
                current_brand = current_title.split()[0]
                if current_brand.lower() not in [b.lower() for b in title_patterns['brands']]:
                    # 使用最常见的品牌名
                    most_common_brand = max(title_patterns['brands'], key=lambda x: sum(1 for p in similar_products if x.lower() in p['title'].lower()))
                    optimized_title = f"{most_common_brand} {current_title}"
            
            # 2. 添加缺失的重要特征词
            missing_features = [f for f in title_patterns['features'] if f not in optimized_title.lower()]
            if missing_features:
                # 选择评分最高的产品的特征词
                top_rated_product = max(similar_products, key=lambda x: float(x['rating']))
                top_features = [f for f in title_patterns['features'] if f in top_rated_product['title'].lower()]
                if top_features:
                    optimized_title = f"{optimized_title} - {top_features[0].title()}"
            
            # 3. 添加重要规格（如果缺失）
            current_specs = set(re.findall(r'\((.*?)\)', optimized_title))
            missing_specs = [s for s in title_patterns['specs'] if s not in current_specs]
            if missing_specs:
                # 选择销量最高的产品的规格
                top_selling_product = max(similar_products, key=lambda x: float(x['sales_info'].replace(',', '')))
                top_specs = [s for s in title_patterns['specs'] if s in top_selling_product['title']]
                if top_specs:
                    optimized_title = f"{optimized_title} ({top_specs[0]})"
            
            # 4. 确保标题长度合适
            if len(optimized_title) > 200:
                optimized_title = optimized_title[:197] + "..."
            
            return {
                'original_title': current_title,
                'optimized_title': optimized_title,
                'analysis': {
                    'category': search_category,
                    'similar_products_analyzed': len(similar_products),
                    'common_brands': list(title_patterns['brands']),
                    'common_features': list(title_patterns['features']),
                    'common_specs': list(title_patterns['specs']),
                    'top_keywords': list(title_patterns['keywords'])[:10]
                }
            }
            
        except Exception as e:
            print(f"优化标题时出错: {str(e)}")
            return {'error': str(e)}

    def extract_long_tail_keywords(self, product_data: Dict[str, Any]) -> List[str]:
        """提取核心长尾词"""
        try:
            long_tail_keywords = set()
            
            # 从标题中提取长尾词
            if product_data.get('title'):
                title = product_data['title'].lower()
                # 提取括号中的内容
                if '(' in title and ')' in title:
                    long_tail_keywords.update(re.findall(r'\((.*?)\)', title))
                # 提取连字符连接的内容
                long_tail_keywords.update(re.findall(r'([a-z]+-[a-z]+)', title))
            
            # 从五点描述中提取长尾词
            if product_data.get('details'):
                for detail in product_data['details']:
                    detail = detail.lower()
                    # 提取冒号后的内容
                    if ':' in detail:
                        long_tail_keywords.update(detail.split(':')[1].strip().split())
                    # 提取括号中的内容
                    if '(' in detail and ')' in detail:
                        long_tail_keywords.update(re.findall(r'\((.*?)\)', detail))
                    # 提取连字符连接的内容
                    long_tail_keywords.update(re.findall(r'([a-z]+-[a-z]+)', detail))
            
            # 清理和过滤长尾词
            long_tail_keywords = {
                keyword.strip('.,!?()[]{}"\'') 
                for keyword in long_tail_keywords 
                if len(keyword) > 3 and ' ' in keyword  # 确保是长尾词
            }
            
            return list(long_tail_keywords)
            
        except Exception as e:
            print(f"提取长尾词时出错: {str(e)}")
            return []

    def find_similar_products_by_keywords(self, long_tail_keywords: List[str], limit: int = 50) -> List[Dict[str, Any]]:
        """根据长尾词查找相似产品"""
        try:
            if not long_tail_keywords:
                return []
            
            # 构建关键词搜索条件
            keyword_conditions = []
            for keyword in long_tail_keywords:
                keyword_conditions.append(f"(p.title LIKE '%{keyword}%' OR p.detail1 LIKE '%{keyword}%' OR p.detail2 LIKE '%{keyword}%' OR p.detail3 LIKE '%{keyword}%' OR p.detail4 LIKE '%{keyword}%' OR p.detail5 LIKE '%{keyword}%')")
            
            # 构建查询
            query = f"""
            SELECT 
                p.productid,
                p.title,
                p.detail1,
                p.detail2,
                p.detail3,
                p.detail4,
                p.detail5,
                p.rating,
                p.reviews_count,
                p.sales_info,
                p.main_rank
            FROM app_product_xue p
            WHERE {' OR '.join(keyword_conditions)}
            ORDER BY 
                p.rating DESC,
                p.reviews_count DESC,
                p.sales_info DESC
            LIMIT {limit}
            """
            
            similar_products = self.excelhelper_instance.query_all(query)
            return similar_products
            
        except Exception as e:
            print(f"查找相似产品时出错: {str(e)}")
            return []

    def optimize_product_content(self, product_data: Dict[str, Any]) -> Dict[str, Any]:
        """优化产品标题和五点描述"""
        try:
            # 提取长尾词
            long_tail_keywords = self.extract_long_tail_keywords(product_data)
            if not long_tail_keywords:
                return {'error': '未找到有效的长尾词'}
            
            # 查找相似产品
            similar_products = self.find_similar_products_by_keywords(long_tail_keywords)
            if not similar_products:
                return {'error': '未找到相似产品'}
            
            # 分析相似产品的标题模式
            title_patterns = {
                'keywords': set(),
                'features': set(),
                'brands': set(),
                'specs': set()
            }
            
            # 分析相似产品的五点描述模式
            bullet_patterns = {
                'features': set(),
                'benefits': set(),
                'specs': set(),
                'keywords': set()
            }
            
            for product in similar_products:
                # 分析标题
                title = product['title'].lower()
                # 提取品牌名
                brand = title.split()[0]
                if brand.isupper() or brand[0].isupper():
                    title_patterns['brands'].add(brand)
                # 提取规格
                specs = re.findall(r'\((.*?)\)', title)
                title_patterns['specs'].update(specs)
                # 提取特征词
                feature_words = {
                    'premium', 'professional', 'advanced', 'deluxe',
                    'high quality', 'best', 'top', 'excellent',
                    'durable', 'long lasting', 'reliable', 'sturdy'
                }
                for word in feature_words:
                    if word in title:
                        title_patterns['features'].add(word)
                
                # 分析五点描述
                for i in range(1, 6):
                    detail = product.get(f'detail{i}', '').lower()
                    if detail:
                        # 提取特征
                        if ':' in detail:
                            feature = detail.split(':')[0].strip()
                            bullet_patterns['features'].add(feature)
                        # 提取优势
                        if 'for' in detail or 'with' in detail:
                            benefit = detail.split('for')[1].strip() if 'for' in detail else detail.split('with')[1].strip()
                            bullet_patterns['benefits'].add(benefit)
                        # 提取规格
                        specs = re.findall(r'\((.*?)\)', detail)
                        bullet_patterns['specs'].update(specs)
                        # 提取关键词
                        words = detail.split()
                        bullet_patterns['keywords'].update(words)
            
            # 优化标题
            current_title = product_data['title']
            optimized_title = current_title
            
            # 1. 添加缺失的重要特征词
            missing_features = [f for f in title_patterns['features'] if f not in optimized_title.lower()]
            if missing_features:
                # 选择评分最高的产品的特征词
                top_rated_product = max(similar_products, key=lambda x: float(x['rating']))
                top_features = [f for f in title_patterns['features'] if f in top_rated_product['title'].lower()]
                if top_features:
                    optimized_title = f"{optimized_title} - {top_features[0].title()}"
            
            # 2. 添加重要规格
            current_specs = set(re.findall(r'\((.*?)\)', optimized_title))
            missing_specs = [s for s in title_patterns['specs'] if s not in current_specs]
            if missing_specs:
                # 选择销量最高的产品的规格
                top_selling_product = max(similar_products, key=lambda x: float(x['sales_info'].replace(',', '')))
                top_specs = [s for s in title_patterns['specs'] if s in top_selling_product['title']]
                if top_specs:
                    optimized_title = f"{optimized_title} ({top_specs[0]})"
            
            # 优化五点描述
            optimized_bullets = []
            for i in range(1, 6):
                current_bullet = product_data.get(f'detail{i}', '')
                if not current_bullet:
                    continue
                
                # 构建优化后的描述点
                optimized_bullet = current_bullet
                
                # 1. 添加特征词
                if bullet_patterns['features']:
                    feature = next(iter(bullet_patterns['features']), '')
                    if feature and feature not in optimized_bullet.lower():
                        optimized_bullet = f"{feature.title()}: {optimized_bullet}"
                
                # 2. 添加优势描述
                if bullet_patterns['benefits']:
                    benefit = next(iter(bullet_patterns['benefits']), '')
                    if benefit and benefit not in optimized_bullet.lower():
                        optimized_bullet = f"{optimized_bullet} for {benefit}"
                
                # 3. 添加规格信息
                if bullet_patterns['specs']:
                    spec = next(iter(bullet_patterns['specs']), '')
                    if spec and spec not in optimized_bullet:
                        optimized_bullet = f"{optimized_bullet} ({spec})"
                
                optimized_bullets.append(optimized_bullet)
            
            return {
                'original_title': current_title,
                'optimized_title': optimized_title,
                'original_bullets': [product_data.get(f'detail{i}', '') for i in range(1, 6)],
                'optimized_bullets': optimized_bullets,
                'long_tail_keywords': long_tail_keywords,
                'analysis': {
                    'similar_products_analyzed': len(similar_products),
                    'title_patterns': {k: list(v) for k, v in title_patterns.items()},
                    'bullet_patterns': {k: list(v) for k, v in bullet_patterns.items()}
                }
            }
            
        except Exception as e:
            print(f"优化产品内容时出错: {str(e)}")
            return {'error': str(e)}

# 使用示例
if __name__ == "__main__":
    system = BigModelSystem()
    
    try:
        # 启动调度器
        system.start_scheduler()
        
        # 主线程可以继续执行其他任务
        while True:
            time.sleep(1)
            
    except KeyboardInterrupt:
        # 优雅地停止调度器
        system.stop_scheduler()
        # 启动调度器
        system.start_scheduler()
        
        # 主线程可以继续执行其他任务
        while True:
            time.sleep(1)
            
    except KeyboardInterrupt:
        # 优雅地停止调度器
        system.stop_scheduler()
        
        # 主线程可以继续执行其他任务
        while True:
            time.sleep(1)
            
    except KeyboardInterrupt:
        # 优雅地停止调度器
        system.stop_scheduler()